unit uFrmGridEdit;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ComCtrls, Oracle, StdCtrls, Grids, Buttons, ExtCtrls, StrUtils, gnugettext, OracleData,
  DBCtrls, DB, SeNumEdit;

type
  TFrmGridEditForm = class(TForm)
    pnl_Bottom: TPanel;
    pnl_Buttons: TPanel;
    btn_Save: TSpeedButton;
    btn_Close: TSpeedButton;
    pnl_CloneTournament: TPanel;
    Label1: TLabel;
    chb_CloneRounds: TCheckBox;
    chb_CloneTables: TCheckBox;
    DataSource1: TDataSource;
    OracleDataSet1: TOracleDataSet;
    SENumEdit1: TSENumEdit;
    procedure btn_CloseClick(Sender: TObject);
    procedure btn_SaveClick(Sender: TObject);
    procedure FormKeyPress(Sender: TObject; var Key: Char);
    procedure FormCreate(Sender: TObject);
  private
    { Private declarations }
    EditFieldColor: TColor;
    m_IsEdit, m_DuplicateValue: boolean;
    m_Grid: TStringGrid;
    com_WinDist: TDBLookupComboBox;
    procedure PrepareDataForInsertFromEditBoxes();
    procedure PrepareDataForUpdateFromEditBoxes();
    function GetComponentValue(ComIndex: Integer): string;
    procedure InsertDetailData(DbTable, DbWhereField: string);
    procedure PopulateWinDistCombo();
  public
    m_DbTable, m_TableIDnew, m_TableIDorig: string;
    procedure DeleteAllEditComponents();
    procedure ShowGridEditForm(OraQuery: TOracleQuery; Grid: TStringGrid; IsEdit: boolean; DbTable: String; DuplicateValue: boolean);
    procedure SetEditBoxesValueFromGrid(Col, Row: Integer; SetValueToNull: boolean = false); { Public declarations }
    procedure CreateEditBoxes(OraQuery: TOracleQuery);
  end;

var
  FrmGridEditForm: TFrmGridEditForm;

const
  LabelFieldLeft: Integer = 10;
  ComponentsLeft: Integer = 150;
  ComponentsHeight: Integer = 30;

implementation

{$R *.dfm}
{ TForm1 }

procedure TFrmGridEditForm.PrepareDataForUpdateFromEditBoxes();
var
  c: Integer;
  SQLUpdate, ComponentValue: string;
begin
  for c := 1 to m_Grid.ColCount - 1 do
  begin
    ComponentValue := GetComponentValue(c);
    begin
      if pos('to_date', ComponentValue) > 0 then
      begin
        SQLUpdate := SQLUpdate + m_Grid.Cells[c, 0] + '=' + ComponentValue + ', ';
      end
      else
      begin
        SQLUpdate := SQLUpdate + m_Grid.Cells[c, 0] + '=' + '''' + ComponentValue + '''' + ', ';
      end;
    end;
  end;
  SQLUpdate := MidStr(SQLUpdate, 0, Length(SQLUpdate) - 2);
  // FrmMain.UpdateDataToDB(m_DbTable, SQLUpdate, 'ID=' + m_Grid.Cells[0, m_Grid.Row]);

end;

function TFrmGridEditForm.GetComponentValue(ComIndex: Integer): string;
var
  AComponent, BComponent: TComponent;
  DateTime: string;
begin
  result := '';
  AComponent := FindComponent('edt_col' + inttostr(ComIndex));
  if AComponent = nil then
  begin
    AComponent := FindComponent('chb_col' + inttostr(ComIndex));
    if AComponent <> nil then
      result := BToStr((AComponent as TCheckBox).Checked);
  end
  else
  begin
    result := (AComponent as TEdit).Text;
  end;
  if AComponent = nil then
  begin
    AComponent := FindComponent('date_col' + inttostr(ComIndex));
    BComponent := FindComponent('time_col' + inttostr(ComIndex));
    if (AComponent <> nil) then
    begin
      if BComponent = nil then
      begin
        DateTime := FormatDateTime('dd.mm.yyyy', (AComponent as TDateTimePicker).Date);
        result := 'to_date(''' + DateTime + ''', ''dd.mm.yyyy'')';
      end
      else
      begin
        DateTime := FormatDateTime('dd.mm.yyyy', (AComponent as TDateTimePicker).Date);
        DateTime := DateTime + ' ' + FormatDateTime('hh:nn:ss', (BComponent as TDateTimePicker).Time);
        begin
          result := 'to_date(''' + DateTime + ''', ''dd.mm.yyyy hh24:mi:ss'')';
        end;
      end;
    end;
  end;
  if AComponent = nil then
  begin
    AComponent := FindComponent('com_col' + inttostr(ComIndex));
    if AComponent <> nil then
    begin
      result := VarToStr((AComponent as TDBLookupComboBox).KeyValue);
    end;
  end;
end;

procedure TFrmGridEditForm.DeleteAllEditComponents();
var
  i: Integer;
begin
  // delete all editboxes
  for i := self.ControlCount - 1 downto 0 do
  begin
    if (self.Controls[i] is TLabel) then
    begin
      self.Controls[i].Free;
    end
    else if (self.Controls[i] is TEdit) then
    begin
      self.Controls[i].Free;
    end
    else if (self.Controls[i] is TDateTimePicker) then
    begin
      self.Controls[i].Free;
    end
    else if (self.Controls[i] is TCheckBox) then
    begin
      self.Controls[i].Free;
    end
    else if (self.Controls[i] is TDBLookupComboBox) then
    begin
      self.Controls[i].Free;
    end;
  end;
end;

procedure TFrmGridEditForm.FormCreate(Sender: TObject);
begin
  gnugettext.TranslateComponent(self);
end;

procedure TFrmGridEditForm.FormKeyPress(Sender: TObject; var Key: Char);
begin
  // The Escape key is #27.
  if Key = #27 then
    Close;
end;

procedure TFrmGridEditForm.btn_CloseClick(Sender: TObject);
begin
  Close();
end;

procedure TFrmGridEditForm.btn_SaveClick(Sender: TObject);
begin
  if m_DuplicateValue then
  begin
    PrepareDataForInsertFromEditBoxes();
    if m_DbTable = 'TOURNAMENTS' then
    begin
      if chb_CloneRounds.Checked then
      begin
        InsertDetailData('TOURNAMENT_ROUNDS', 'TOURNAMENT_ID');
      end;
      // clone tables
      if chb_CloneTables.Checked then
      begin
        InsertDetailData('TOURNAMENT_TABLES', 'TOURNAMENT_ID');
      end;
    end;
  end
  else
  begin
    if m_IsEdit then
    begin
      PrepareDataForUpdateFromEditBoxes();
    end
    else
    begin
      PrepareDataForInsertFromEditBoxes();
    end;
  end;
  if m_DbTable = 'TOURNAMENTS' then
  begin
    if m_DuplicateValue then
    begin
      if m_DbTable = 'TOURNAMENTS' then
        // FrmMain.InsertDataIntoDB('TOURNAMENT_STOPS', 'TOURNAMENT_ID, STOP_FROM, STOP_TO', m_Grid.Cells[0, m_Grid.Row] + ', ' + '(select event_start from tournaments where id=' + m_Grid.Cells
        // [0, m_Grid.Row] + '), ' + '''' + '''');
      end
    else
    begin
      // if m_IsEdit then
      // FrmMain.UpdateDataToDB('TOURNAMENT_STOPS', 'STOP_FROM=' + '(select event_start from tournaments where id=' + m_Grid.Cells[0, m_Grid.Row] + ')', 'tournament_id=' + m_Grid.Cells[0, m_Grid.Row])
      // else
      // FrmMain.InsertDataIntoDB('TOURNAMENT_STOPS', 'TOURNAMENT_ID, STOP_FROM, STOP_TO', m_TableIDnew + ', ' + '(select event_start from tournaments where id=' + m_TableIDnew + '), ' + '''' + '''');
    end;
  end;
  FrmGridEditForm.SetEditBoxesValueFromGrid(m_Grid.Col, m_Grid.Row, true);
//  m_AdmGrid.SetGridData(m_DbTable, m_AdmGrid.LastQuery, FrmMain.OracleSession1);
  Close();
end;

procedure TFrmGridEditForm.InsertDetailData(DbTable, DbWhereField: string);
var
  Query, FieldNames, FieldValues: string;
  c: Integer;
  OracleQuery: TOracleQuery;
begin
  OracleQuery := TOracleQuery.Create(self);
 // OracleQuery.Session := FrmMain.OracleSession1;
  Query := 'select * from ' + DbTable + ' where ' + DbWhereField + '=' + m_TableIDorig;
  OracleQuery.SQL.Clear;
  OracleQuery.SQL.Add(Query);
  OracleQuery.Execute;
  while not OracleQuery.Eof do
  begin
    FieldNames := '';
    FieldValues := '';
    for c := 1 to OracleQuery.FieldCount - 1 do
    begin
      FieldNames := FieldNames + OracleQuery.FieldName(c) + ', ';
      if OracleQuery.FieldName(c) = DbWhereField then
      begin
        FieldValues := FieldValues + '''' + m_TableIDnew + '''' + ', ';
      end
      else
      begin
        FieldValues := FieldValues + '''' + VarToStr(OracleQuery.Field(c)) + '''' + ', ';
      end;
    end;
    FieldNames := MidStr(FieldNames, 0, Length(FieldNames) - 2);
    FieldValues := MidStr(FieldValues, 0, Length(FieldValues) - 2);
 //   FrmMain.InsertDataIntoDB(DbTable, FieldNames, FieldValues);
    OracleQuery.Next;
  end;
  OracleQuery.Close;
  FreeAndNil(OracleQuery);
end;

procedure TFrmGridEditForm.PrepareDataForInsertFromEditBoxes();
var
  c: Integer;
  SQLValues, SQLFields, ComponentValue: string;
begin
  SQLValues := '';
  SQLFields := '';
  for c := 1 to m_Grid.ColCount - 1 do
  begin
    if m_Grid.ColWidths[c] = -1 then
      continue;
    ComponentValue := GetComponentValue(c);
    SQLFields := SQLFields + m_Grid.Cells[c, 0] + ', ';
    if pos('to_date', ComponentValue) > 0 then
    begin
      SQLValues := SQLValues + ComponentValue + ', ';
    end
    else
    begin
      SQLValues := SQLValues + '''' + ComponentValue + '''' + ', ';
    end;
  end;
  // --------
  if m_DbTable = 'TOURNAMENT_ROUNDS' then
  begin
    SQLFields := SQLFields + 'TOURNAMENT_ID' + ', ';
//    SQLValues := SQLValues + '''' + FrmMain.adm_Tournaments.stg_GridData.Cells[0, FrmMain.adm_Tournaments.stg_GridData.Row] + '''' + ', ';
  end;
  if m_DbTable = 'PLAYER_NOTES' then
  begin
    SQLFields := SQLFields + 'PLAYER_ID' + ', ';
  //  SQLValues := SQLValues + '''' + FrmMain.FraPlayersMain.adm_Players.stg_GridData.Cells[0, FrmMain.FraPlayersMain.adm_Players.stg_GridData.Row] + '''' + ', ';
  end;
  if m_DbTable = 'USERS' then
  begin
    SQLFields := SQLFields + 'PASSWORD' + ', ';
    SQLValues := SQLValues + '''' + '''' + ', ';
  end;
  if m_DbTable = 'WIN_PERCENTAGE' then
  begin
    SQLFields := SQLFields + 'WIN_PLAYERS_NUMBER_ID' + ', ';
 //   SQLValues := SQLValues + '''' + FrmMain.adm_WinNumPlayers.stg_GridData.Cells[0, FrmMain.adm_WinNumPlayers.stg_GridData.Row] + '''' + ', ';
  end;
  // --------
  SQLFields := MidStr(SQLFields, 0, Length(SQLFields) - 2);
  SQLValues := MidStr(SQLValues, 0, Length(SQLValues) - 2);
//  m_TableIDnew := FrmMain.InsertDataIntoDB(m_DbTable, SQLFields, SQLValues);
end;

procedure TFrmGridEditForm.CreateEditBoxes(OraQuery: TOracleQuery);
var
  edt_box: TEdit;
  dp_box, tp_box: TDateTimePicker;
  chb_box: TCheckBox;
  lbl_field: TLabel;
  c: Integer;
  ComponentsWidth: Integer;
begin
  DeleteAllEditComponents();
  ComponentsWidth := 200;
  EditFieldColor := $00DFE7D3;
  for c := 0 to OraQuery.FieldCount - 1 do
  begin
    if m_Grid.ColWidths[c] = -1 then
      continue;
    if (m_DbTable = 'TOURNAMENTS') and (OraQuery.FieldName(c) = 'WIN_DISTRIBUTION_ID') then
    begin
      com_WinDist := TDBLookupComboBox.Create(self);
      com_WinDist.Width := ComponentsWidth;
      com_WinDist.Top := ComponentsHeight * c;
      com_WinDist.Name := 'com_col' + inttostr(c);
      com_WinDist.Left := ComponentsLeft;
      com_WinDist.Parent := self;
      com_WinDist.Color := EditFieldColor;
      PopulateWinDistCombo();
      // label
      lbl_field := TLabel.Create(self);
      lbl_field.Width := ComponentsWidth;
      lbl_field.Top := ComponentsHeight * c;
      lbl_field.Name := 'lbl_field' + inttostr(c);
      lbl_field.Left := LabelFieldLeft;
      lbl_field.Parent := self;
      continue;
    end;
    // label
    lbl_field := TLabel.Create(self);
    lbl_field.Width := ComponentsWidth;
    lbl_field.Top := ComponentsHeight * c;
    lbl_field.Name := 'lbl_field' + inttostr(c);
    lbl_field.Left := LabelFieldLeft;
    lbl_field.Parent := self;
    if OraQuery.FieldDBType(c) = 12 then
    begin
      // date
      dp_box := TDateTimePicker.Create(self);
      dp_box.Kind := dtkDate;
      dp_box.Width := ComponentsWidth;
      dp_box.Top := ComponentsHeight * c;
      dp_box.Name := 'date_col' + inttostr(c);
      dp_box.Left := ComponentsLeft;
      dp_box.Parent := self;
      dp_box.Color := EditFieldColor;
      // time
      if m_DbTable <> 'PLAYERS' then
      begin
        tp_box := TDateTimePicker.Create(self);
        tp_box.Kind := dtkTime;
        tp_box.Width := ComponentsWidth div 2;
        tp_box.Top := ComponentsHeight * c;
        tp_box.Name := 'time_col' + inttostr(c);
        tp_box.Left := ComponentsLeft + ComponentsWidth div 2;
        tp_box.Parent := self;
        dp_box.Width := ComponentsWidth div 2;
      end;
    end
    else if OraQuery.FieldDBType(c) = 96 then
    begin
      // char boolean(disabled)
      chb_box := TCheckBox.Create(self);
      chb_box.Width := ComponentsWidth;
      chb_box.Top := ComponentsHeight * c;
      chb_box.Name := 'chb_col' + inttostr(c);
      chb_box.Caption := '';
      chb_box.Left := ComponentsLeft;
      chb_box.Parent := self;
      chb_box.Color := EditFieldColor;
    end
    else
    begin
      if (OraQuery.FieldDBType(c) = 2) and MatchStr(OraQuery.FieldName(c), ['VALUE', 'BUY', 'FEE', 'RAKE']) then
      begin
        edt_box := TSENumEdit.Create(self);
        edt_box.Width := ComponentsWidth;
        edt_box.Top := ComponentsHeight * c;
        edt_box.Name := 'edt_col' + inttostr(c);
        edt_box.Left := ComponentsLeft;
        edt_box.Parent := self;
        edt_box.Color := EditFieldColor;
        edt_box.Text := '';
      end
      else if (OraQuery.FieldDBType(c) = 2) then
      begin
        edt_box := TEdit.Create(self);
        edt_box.Width := ComponentsWidth;
        edt_box.Top := ComponentsHeight * c;
        edt_box.Name := 'edt_col' + inttostr(c);
        edt_box.Left := ComponentsLeft;
        edt_box.Parent := self;
        edt_box.Color := EditFieldColor;
        edt_box.Text := '';
        edt_box.NumbersOnly := true;
      end
      else
      begin
        edt_box := TEdit.Create(self);
        edt_box.Width := ComponentsWidth;
        edt_box.Top := ComponentsHeight * c;
        edt_box.Name := 'edt_col' + inttostr(c);
        edt_box.Left := ComponentsLeft;
        edt_box.Parent := self;
        edt_box.Color := EditFieldColor;
        edt_box.Text := '';
      end;
    end;
  end;
  inc(c);
  if pnl_CloneTournament.Visible then
    self.Height := ComponentsHeight * c + 50 + pnl_Buttons.Height + pnl_CloneTournament.Height
  else
    self.Height := ComponentsHeight * c + 50 + pnl_Buttons.Height;
end;

procedure TFrmGridEditForm.SetEditBoxesValueFromGrid(Col, Row: Integer; SetValueToNull: boolean = false);
var
  c: Integer;
  AComponent: TComponent;
begin
  for c := 0 to m_Grid.ColCount - 1 do
  begin
    if m_Grid.ColWidths[c] = -1 then
      continue;
    // LABEL FIELD
    AComponent := FindComponent('lbl_field' + inttostr(c));
    if AComponent <> nil then
    begin
      TLabel(AComponent).Caption := m_Grid.Cells[c, 1];
    end;
    // EDIT
    AComponent := FindComponent('edt_col' + inttostr(c));
    if AComponent <> nil then
    begin
      if SetValueToNull then
        TEdit(AComponent).Text := ''
      else
        TEdit(AComponent).Text := m_Grid.Cells[c, Row];
    end;
    // COMBO
    AComponent := FindComponent('chb_col' + inttostr(c));
    if AComponent <> nil then
    begin
      if SetValueToNull then
      begin
        TCheckBox(AComponent).Checked := false;
      end
      else
      begin
        if m_Grid.Cells[c, Row] = '0' then
        begin
          TCheckBox(AComponent).Checked := false;
        end
        else if m_Grid.Cells[c, Row] = '1' then
        begin
          TCheckBox(AComponent).Checked := true;
        end;
      end;
    end;
    // DATE
    AComponent := FindComponent('date_col' + inttostr(c));
    if AComponent <> nil then
    begin
      if SetValueToNull then
      begin
        TDateTimePicker(AComponent).Date := now();
      end
      else
      begin
        if m_Grid.Cells[c, Row] <> '' then
        begin
          TDateTimePicker(AComponent).Date := StrToDateTime(m_Grid.Cells[c, Row]);
        end;
      end;
    end;
    // TIME
    AComponent := FindComponent('time_col' + inttostr(c));
    if AComponent <> nil then
    begin
      if SetValueToNull then
      begin
        TDateTimePicker(AComponent).Date := now();
      end
      else
      begin
        if m_Grid.Cells[c, Row] <> '' then
        begin
          TDateTimePicker(AComponent).Time := StrToDateTime(m_Grid.Cells[c, Row]);
        end;
      end;
    end;
    // COMBOBOX
    AComponent := FindComponent('com_col' + inttostr(c));
    if AComponent <> nil then
    begin
      if SetValueToNull then
      begin
        TDBLookupComboBox(AComponent).KeyValue := null;
      end
      else
      begin
        if m_Grid.Cells[c, Row] <> '' then
        begin
          TDBLookupComboBox(AComponent).KeyValue := m_Grid.Cells[c, Row];
        end;
      end;
    end;
  end;
end;

procedure TFrmGridEditForm.PopulateWinDistCombo();
begin
//  OracleDataSet1.Session := FrmMain.OracleSession1;
  OracleDataSet1.SQL.Clear;
  OracleDataSet1.SQL.Add('select * from WIN_DISTRIBUTION where disabled=0 union select null, null, null from WIN_DISTRIBUTION order by 1 desc');
  // ---
  OracleDataSet1.Open;
  DataSource1.DataSet := OracleDataSet1;
  com_WinDist.ListSource := DataSource1;
  com_WinDist.KeyField := 'ID';
  com_WinDist.ListField := 'NAME';
end;

procedure TFrmGridEditForm.ShowGridEditForm(OraQuery: TOracleQuery; Grid: TStringGrid; IsEdit: boolean; DbTable: String; DuplicateValue: boolean);
begin
  m_DbTable := DbTable;
  m_Grid := Grid;
  pnl_CloneTournament.Visible := false;
  CreateEditBoxes(OraQuery);
  m_DuplicateValue := DuplicateValue;
  if DuplicateValue then
  begin
    self.Caption := 'Clone ' + MidStr(DbTable, 0, Length(DbTable) - 1);
    SetEditBoxesValueFromGrid(m_Grid.Col, m_Grid.Row);
  end
  else
  begin
    if IsEdit then
    begin
      m_IsEdit := true;
      self.Caption := 'Edit ' + MidStr(DbTable, 0, Length(DbTable) - 1);
      SetEditBoxesValueFromGrid(m_Grid.Col, m_Grid.Row);
    end
    else
    begin
      m_IsEdit := false;
      self.Caption := 'New ' + MidStr(DbTable, 0, Length(DbTable) - 1);
      SetEditBoxesValueFromGrid(m_Grid.Col, m_Grid.Row, true);
    end;
  end;
  ShowModal();
end;

end.
